﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.GERP.GHRMS.DAL;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IBLL; 
using KPIS.DBM;

using KPIS.GERP.GHRMS.DALFactory;

namespace KPIS.GERP.GHRMS.BLL
{
    public class WelfareFinanceBLL


    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);



        private string formatDate(string value)
        {
            string tmp = null;
            string[] tmp2 = null;
            tmp2 = value.Split('/');
            tmp = tmp2[1] + "/" + tmp2[0] + "/" + tmp2[2];   

            return tmp;
        }

        //WF_FinanceInfo

        public void insert(string finance_name, string remarks, int created_by)
        {
            try
            {

                dbManager.Open();
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@finance_name", finance_name);
                dbManager.AddParameters(1, "@remarks", remarks);

                dbManager.AddParameters(2, "@record_status",SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(3, "@created_by", created_by);
                dbManager.AddParameters(4, "@created_when", DateTime.Today);


                string sql;

                sql = " insert into "
                + " hrm_wf_finances "
                + " (finance_name,"
                + " remarks,"
                + " record_status,"
                + " created_by,"
                + " created_when)"

                + " values "
                + " (@finance_name,"
                + " @remarks,"

                + " @record_status,"
                + " @created_by,"
                + " @created_when)";


                dbManager.ExecuteNonQuery(CommandType.Text, sql);    

            }

            catch(Exception ex)
             {

                 throw new Exception(ex.Message);  
            }

          finally 
                 {
                     dbManager.Dispose(); 

            }

        }

        public void Update(int finance_seq, string finance_name, string remarks, int updated_by)
        {
            try
            {
                
                dbManager.Open();
                dbManager.CreateParameters(6);
                dbManager.AddParameters(0, "@finance_seq", finance_seq);
                dbManager.AddParameters(1, "@finance_name", finance_name);
                dbManager.AddParameters(2, "@remarks", remarks);
                dbManager.AddParameters(3, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(4, "@updated_by", updated_by);
                dbManager.AddParameters(5, "@updated_when", DateTime.Today);


                string sql;

                sql = " update "
                + " hrm_wf_finances "
                + " set "
                + " finance_name=@finance_name,"
                + " remarks=@remarks,"
                + " record_status=@record_status,"
                + " updated_by=@updated_by, "
                + " updated_when=@updated_when"
                +" where finance_seq=@finance_seq";


                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }

            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }

            finally
            {
                dbManager.Dispose();

            }

        }
        
        public void Delete(int finance_seq, int UpdatedBy)
        {
            try
            {

                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@finance_seq", finance_seq);
                dbManager.AddParameters(2, "@updated_by", UpdatedBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);

                string sql;

                sql = " update hrm_wf_finances set "
              + " record_status=@record_status"
              + ", updated_by = @updated_by"
              + ", updated_when = @updated_when"

                + " where finance_seq = @finance_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }
            catch (Exception ex)
            {
                throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        
        public DataTable Select()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT * from hrm_wf_finances "

                    + " where record_status = '" + SystemInfo._ActiveRecordStatus + "'";

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }

        public Boolean chkDupFinance(string finance_name)
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = "select * from hrm_wf_finances"
                 
                    + " where finance_name = '" + finance_name + "'"
                    + " and record_status='" + SystemInfo._ActiveRecordStatus + "'";
                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                if (dt.Rows.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }



            }

            catch (Exception ex)
            {
                throw (new Exception(ex.Message));
            }

            finally
            {
                dbManager.Dispose();
            }


        }

        public Boolean chkDupFinanceEdit(string finance_name,int finance_seq)
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = "select * from hrm_wf_finances"

                    + " where finance_name = '" + finance_name + "'"
                    + " and finance_seq not like " + finance_seq 
                    + " and record_status='" + SystemInfo._ActiveRecordStatus + "'";
                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                if (dt.Rows.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }



            }

            catch (Exception ex)
            {
                throw (new Exception(ex.Message));
            }

            finally
            {
                dbManager.Dispose();
            }


        }

        public Boolean chkDupLoan(int finance_seq, string loan_name)
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = "select * from hrm_wf_loans"
                    + " where finance_seq = " + finance_seq
                    + " and loan_name = '" + loan_name + "'"
                    + " and record_status='" + SystemInfo._ActiveRecordStatus + "'";
                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                if (dt.Rows.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }



            }

            catch (Exception ex)
            {
                throw (new Exception(ex.Message));
            }

            finally
            {
                dbManager.Dispose();
            }


        }

        public Boolean chkDupLoanEdit(int finance_seq, string loan_name,int loan_seq)
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = "select * from hrm_wf_loans"
                    + " where finance_seq = " + finance_seq
                    + " and loan_name = '" + loan_name + "'"
                       + " and loan_seq not like " + loan_seq 
                    + " and record_status='" + SystemInfo._ActiveRecordStatus + "'";
                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                if (dt.Rows.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }



            }

            catch (Exception ex)
            {
                throw (new Exception(ex.Message));
            }

            finally
            {
                dbManager.Dispose();
            }


        }
        //WF_ProjectLoan

        public void insertLoan(string loan_name, int finance_seq, string remarks, int created_by)
        {
            try
            {

                dbManager.Open();
                dbManager.CreateParameters(6);
                dbManager.AddParameters(0, "@loan_name", loan_name);
                dbManager.AddParameters(1, "@finance_seq", finance_seq);
                dbManager.AddParameters(2, "@remarks", remarks);

                dbManager.AddParameters(3, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(4, "@created_by", created_by);
                dbManager.AddParameters(5, "@created_when", DateTime.Today);


                string sql;

                sql = " insert into "
                + " hrm_wf_loans "
                + " (loan_name,"
                + " finance_seq,"
                + " remarks,"
                + " record_status,"
                + " created_by,"
                + " created_when)"

                + " values "
                + " (@loan_name,"
                + " @finance_seq,"
                + " @remarks," 
                + " @record_status,"
                + " @created_by,"
                + " @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }

            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }

            finally
            {
                dbManager.Dispose();

            }

        }

        public void UpdateLoan(int loan_seq, string loan_name, string remarks, int updated_by)
        {
            try
            {

                dbManager.Open();
                dbManager.CreateParameters(6);
                dbManager.AddParameters(0, "@loan_seq", loan_seq);
                dbManager.AddParameters(1, "@loan_name", loan_name);
                dbManager.AddParameters(2, "@remarks", remarks);
                dbManager.AddParameters(3, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(4, "@updated_by", updated_by);
                dbManager.AddParameters(5, "@updated_when", DateTime.Today);


                string sql;
                
                sql = " update "
                + " hrm_wf_loans "
                + " set "
                + " loan_name=@loan_name,"
                + " remarks=@remarks,"
                + " record_status=@record_status,"
                + " updated_by=@updated_by, "
                + " updated_when=@updated_when"
                + " where loan_seq=@loan_seq";


                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }

            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }

            finally
            {
                dbManager.Dispose();

            }

        }

        public void DeleteLoan(int loan_seq, int UpdatedBy)
        {
            try
            {

                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@loan_seq", loan_seq);
                dbManager.AddParameters(2, "@updated_by", UpdatedBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);

                string sql;

                sql = " update hrm_wf_loans set "
              + " record_status=@record_status"
              + ", updated_by = @updated_by"
              + ", updated_when = @updated_when"

                + " where loan_seq = @loan_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }
            catch (Exception ex)
            {
                throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable SelectLoan(int finance_seq)
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT "
                    + " hrm_wf_finances.finance_seq,"
                    + " hrm_wf_loans.loan_seq,"
                    + " hrm_wf_loans.loan_name,"
                    + " hrm_wf_loans.remarks"

                    + " FROM"
                    + " hrm_wf_finances"
                    + " Inner Join hrm_wf_loans ON hrm_wf_finances.finance_seq = hrm_wf_loans.finance_seq"
                    + " where hrm_wf_loans.record_status = '" + SystemInfo._ActiveRecordStatus + "'"
                +" and hrm_wf_loans.finance_seq=" + finance_seq;

              
                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }
              


    }
}
